/* The dofile obtains summary statistics for processed/cleaned dataset */

u "$data_clean_failure/riskdata_analysis.dta",clear
drop if year==2018     // 4416 obs, problematic subprime loans 
// drop if assets>=10000  // 1219 obs with more than 10 billion assets, too big to fail companies


*--------------------------------------*
*          Global: outcoms             *
*--------------------------------------* 
global Performance "fail delqRatio netChargeOffRatio delqRatio_mort netChargeOffRatio_mort"

gen origination_perasset = originations / assets
la var origination_perasset "origination per 1 million assets"

global Loan "pct_subprime pct_subprime_conv applications originations origination_perasset approval_rate"


*--------------------------------------*
*          Global: controls            *
*--------------------------------------* 
** institutionl conrols
global Size "assets marketShare numBranch numState"
global Portfolio "pctComm pctRes pctCons pctAg"
global Lendfund "coreDeposits capAd"
global Secondary "pct_sold_to_secondary pct_sold_to_secondary_mort" 
global Profit "roa"
global Institution $Size $Portfolio $Lendfund $Secondary $Profit

** state controls
global State "avgSaHPI unemployment hpiGrowth"

** borrower controls
global Borrower "avg_income avg_loan_amt pct_male pct_white pct_black"

global Controls $Institution $Borrower $State



* ------------------------------------------ *
*    				loan portfolio			   		   *
* ------------------------------------------ *
local sum_results ""
foreach type in bk cu{
	estpost tabstat $Portfolio pct_purchase_loans pct_home_equity_loans pct_refi_loans if  instType=="`type'",  stat(count mean sd) columns(statistics) 
	eststo `type'
	local sum_results "`sum_results' `type'"
}
noisily: esttab `sum_results' using "$outputs_failure/sumtables/sum_portfolio.tex", ///
	replace lab style(tex) alignment(c) ///
	cells((mean(fmt(3)) sd(fmt(3)) count(fmt(0)))) ///
	nonumber nomtitle noobs noline fragment ///
	collabels(none) 



* ------------------------------------------ *
*    Summary stat on performance   *
* ------------------------------------------ *
gen before = year<=2007
gen during = year >=2008 & year<=2012
gen after = year >= 2013 & year <=2017
local Period "before during after"
local Types "bank cu"

quietly{
	eststo clear
	local sum_results ""
	foreach period of local Period{
		foreach type in bk cu{
			estpost tabstat $Performance if `period'==1 & instType=="`type'",  stat(count mean sd) columns(statistics) 
			eststo `period'_`type'
			local sum_results "`sum_results' `period'_`type'"
		}
	}
	noisily: esttab `sum_results' using "$outputs_failure/sumtables/sum_Performance.tex", ///
		replace lab style(tex) alignment(c) ///
		cells((mean(fmt(3)) count(fmt(0))) sd(par fmt(3))) ///
		nonumber nomtitle noobs noline fragment ///
		collabels(none) 
 } 

 
 * ------------------------------------------ *
*    		  Summary stat on loan   			*
* ------------------------------------------ *
replace before = year<=2009
replace after = year > 2009 
local Period "before after"
local Types "bank cu"

quietly{
	eststo clear
	local sum_results ""
	foreach period of local Period{
		foreach type in bk cu{
			estpost tabstat $Loan if `period'==1 &  instType=="`type'",  stat(count mean sd) columns(statistics) 
			eststo `period'_`type'
			local sum_results "`sum_results' `period'_`type'"
		}
	}
	noisily: esttab `sum_results' using "$outputs_failure/sumtables/sum_Loan.tex", ///
		replace lab style(tex) alignment(c) ///
		cells((mean(fmt(3)) count(fmt(0))) sd(par fmt(3))) ///
		nonumber nomtitle noobs noline fragment ///
		collabels(none) 	
} 


 * ------------------------------------------ *
*    	   Summary stat on controls  		*
* ------------------------------------------ *
local Types "bank cu"

quietly{
	foreach controls in Institution Borrower State{
		eststo clear
		local sum_results ""
		foreach type in bk cu{
			estpost tabstat $`controls' if  instType=="`type'",  stat(mean sd) columns(statistics) 
			eststo `type'
			local sum_results "`sum_results' `type'"
		}
		noisily: esttab `sum_results' using "$outputs_failure/sumtables/sum_`controls'.tex", ///
			replace lab style(tex) alignment(c) ///
			cells((mean(fmt(2)) sd(fmt(2)))) ///
			nonumber nomtitle noobs noline fragment ///
			collabels(none) 	
	}
} 





* ------------------------------------------ *
*         			Graphs        			 *
* ------------------------------------------ *
* ssc install grstyle
set scheme s2color
grstyle init
*change the graph size
grstyle graphsize x 3.575
grstyle graphsize y 2.6
*get rid of background shading
grstyle color background white
*use horizontal text for tick labels on the Y (vertical) axis (the s2color default is
*to use vertical text, which makes the labels hard to read)
grstyle anglestyle vertical_tick horizontal
*Also see the schemes provided by Bischof (2017b) that contain, among other things, similar modifications
*Ben Jann 5
*draw vertical grid lines, that is, draw grid lines on the X (horizontal) axis (the
*s2color default is to draw horizontal grid lines only)
grstyle yesno draw_major_hgrid yes
*always include minimum and maximum grid lines (by default, minimum and maximum
*gridlines are omitted if there is no data in the proximity of these grid lines;
*I find this behavior odd, especially if producing a series of graphs that use the
*same scale for the axes for sake of comparability)
grstyle yesno grid_draw_min yes
grstyle yesno grid_draw_max yes
*change color, width, and pattern of grid lines (by default, the gridlines have a
*bluish color, the same color as the background; this no longer makes sense if the
*background shading is removed)
grstyle color major_grid gs8
grstyle linewidth major_grid thin
grstyle linepattern major_grid dot
*place the legend on the lower right of the plot region and remove the frame
grstyle clockdir legend_position 6
grstyle numstyle legend_col 1
grstyle linestyle legend none
*use thicker lines in line plots
grstyle linewidth p medthick
*make markers transparent (only for the first two plot styles for case of exposition;3
*transparency is not supported in Stata 14 or below)
grstyle color p1markline navy%0
grstyle color p1markfill navy%50
grstyle color p2markline maroon%0
grstyle color p2markfill maroon%50
*make confidence intervals transparent (transparency is not supported in Stata 14 or below)
grstyle color ci_area gs12%50
grstyle color ci_arealine gs12%0



* ------------------------------------------ *
*         			Subprime       			 *
* ------------------------------------------ *
** subprime loans: all and secondary (not ready yet)
preserve
foreach x in bank cu{
	bysort year: egen origination_`x' = sum(originations * (`x'==1))
	bysort year: egen subprime_`x' = sum(subprime * (`x'==1)) 
}

collapse (first) origination_* subprime_*, by(year)

foreach x in bank cu{
	gen pct_subprime_`x' = subprime_`x'/origination_`x' * 100
}
list year pct_subprime_*

twoway (scatter pct_subprime_bank year, connect(l)) ///
	   (scatter pct_subprime_cu year, connect(l)), ///
		xline(2007) ///
		xline(2009) ///
		xlabel(2004 "04" 2005 "05" 2006 "06" 2007 "07" 2008 "08" 2009 "09" 2010 "10" 2011 "11" 2012 "12" 2013 "13" 2014 "14" 2015 "15" 2016 "16" 2017 "17", nogrid glcolor(gs14)) ///
		ylabel(0 "0.0%" 3 "3.0%" 6 "6.0%" 9 "9.0%" 12 "12.0%" 15 "15.0%" 18 "18.0%") ///
		xtitle("Year") ytitle("subprime mortgages (%)", size(small)) ///
	    legend(label(1 "Bank") label(2 "Credit Union") size(small)) 
graph export "$outputs_failure/graph_sum/subprime.jpg", replace	
restore



* ------------------------------------------ *
*         			Failure        			 *
* ------------------------------------------ *
preserve
foreach x in bank cu{
	bysort year: egen fail_`x' = sum((`x'==1)* fail)
	bysort year: egen num_`x' = sum((`x'==1)) 
}

collapse (first) num_bank fail_bank num_cu fail_cu, by(year)

foreach x in bank cu{
	gen pct_fail_`x' = fail_`x'/num_`x' * 100
}
list year pct_fail_*

twoway (scatter pct_fail_bank year, connect(l)) ///
	   (scatter pct_fail_cu year, connect(l)), ///
		xline(2007) ///
		xline(2009) ///
		xlabel(2004 "04" 2005 "05" 2006 "06" 2007 "07" 2008 "08" 2009 "09" 2010 "10" 2011 "11" 2012 "12" 2013 "13" 2014 "14" 2015 "15" 2016 "16" 2017 "17", nogrid glcolor(gs14)) ///
		ylabel(0 "0.0%" 0.5 "0.5%" 1 "1.0%" 1.5 "1.5%" 2 "2.0%" 2.5 "2.5%" 3 "3.0%") ///
		xtitle("Year") ytitle("Failure rate (%)", size(small)) ///
	    legend(label(1 "Bank") label(2 "Credit Union") size(small)) 
graph export "$outputs_failure/graph_sum/failure.jpg", replace	
restore




* ------------------------------------------ *
*         		 Delinquencies    			 *
* ------------------------------------------ *
** Delinquency ratio: all, small(<100m), medium (100m-1b), and large (>1b)
preserve
gen all = 1
gen small = assets < 100
gen medium = assets >= 100 & assets < 1000
gen large = assets >= 1000

* data
foreach x in bank cu{
	foreach size in all small medium large{
		bysort year: egen delqmortgage_`x'_`size' = sum((`x'==1) * delqMortgages * (`size'==1)) 
		bysort year: egen mortgage_`x'_`size' = sum((`x'==1)* mortgages * (`size'==1))
	}
}

collapse (first) delqmortgage_* mortgage_*, by(year)

foreach x in bank cu{
	foreach size in all small medium large{
		gen pct_delqmortgage_`x'_`size' = delqmortgage_`x'_`size'/mortgage_`x'_`size' * 100
	}
}
// br year pct_delqmortgage_*


* all
twoway (scatter pct_delqmortgage_bank_all year, connect(l)) ///
	   (scatter pct_delqmortgage_cu_all year, connect(l)), ///
		xline(2007) ///
		xline(2009) ///
		xlabel(2004 "04" 2005 "05" 2006 "06" 2007 "07" 2008 "08" 2009 "09" 2010 "10" 2011 "11" 2012 "12" 2013 "13" 2014 "14" 2015 "15" 2016 "16" 2017 "17", nogrid glcolor(gs14)) ///
		ylabel(0 "0.0%" 1 "1.0%" 2 "2.0%" 3 "3.0%" 4 "4.0%" 5 "5.0%" 6 "6.0%" 7 "7.0%" 8 "8.0%") ///
		xtitle("Year") ytitle("Delinquencies (%)", size(small)) ///
	    legend(label(1 "Bank") label(2 "Credit Union") size(small)) 
graph export "$outputs_failure/graph_sum/delqmortgage.jpg", replace	


* medium
twoway (scatter pct_delqmortgage_bank_medium year, connect(l)) ///
	   (scatter pct_delqmortgage_cu_medium year, connect(l)), ///
		xline(2007) ///
		xline(2009) ///
		xlabel(2004 "04" 2005 "05" 2006 "06" 2007 "07" 2008 "08" 2009 "09" 2010 "10" 2011 "11" 2012 "12" 2013 "13" 2014 "14" 2015 "15" 2016 "16" 2017 "17", nogrid glcolor(gs14)) ///
		ylabel(0 "0.0%" 0.5 "0.5%" 1 "1.0%" 1.5 "1.5%" 2 "2.0%" 2.5 "2.5%" 3 "3.0%") ///
		xtitle("Year") ytitle("Delinquencies (%)", size(small)) ///
	    legend(label(1 "Bank") label(2 "Credit Union") size(small)) 
graph export "$outputs_failure/graph_sum/delqmortgage_medium.jpg", replace	


* large
twoway (scatter pct_delqmortgage_bank_large year, connect(l)) ///
	   (scatter pct_delqmortgage_cu_large year, connect(l)), ///
		xline(2007) ///
		xline(2009) ///
		xlabel(2004 "04" 2005 "05" 2006 "06" 2007 "07" 2008 "08" 2009 "09" 2010 "10" 2011 "11" 2012 "12" 2013 "13" 2014 "14" 2015 "15" 2016 "16" 2017 "17", nogrid glcolor(gs14)) ///
		ylabel(0 "0.0%" 1 "1.0%" 2 "2.0%" 3 "3.0%" 4 "4.0%" 5 "5.0%" 6 "6.0%" 7 "7.0%" 8 "8.0%" 9 "9.0%") ///
		xtitle("Year") ytitle("Delinquencies (%)", size(small)) ///
	    legend(label(1 "Bank") label(2 "Credit Union") size(small)) 
graph export "$outputs_failure/graph_sum/delqmortgage_large.jpg", replace	
restore





* ------------------------------------------ *
*         		Net charge-offs    			 *
* ------------------------------------------ *
** Net charge-off ratio: all, small(<100m), medium (100m-1b), and large (>1b)
preserve
gen all = 1
gen small = assets < 100
gen medium = assets >= 100 & assets < 1000
gen large = assets >= 1000

* data
foreach x in bank cu{
	foreach size in all small medium large{
		bysort year: egen chargeoffmortgage_`x'_`size' = sum((`x'==1) * netChargeMortgages * (`size'==1)) 
		bysort year: egen mortgage_`x'_`size' = sum((`x'==1)* mortgages * (`size'==1))
	}
}

collapse (first) chargeoffmortgage_* mortgage_*, by(year)

foreach x in bank cu{
	foreach size in all small medium large{
		gen pct_chargeoff_`x'_`size' = chargeoffmortgage_`x'_`size'/mortgage_`x'_`size' * 100
	}
}
// br year pct_chargeoff_*


* all
twoway (scatter pct_chargeoff_bank_all year, connect(l)) ///
	   (scatter pct_chargeoff_cu_all year, connect(l)), ///
		xline(2007) ///
		xline(2009) ///
		xlabel(2004 "04" 2005 "05" 2006 "06" 2007 "07" 2008 "08" 2009 "09" 2010 "10" 2011 "11" 2012 "12" 2013 "13" 2014 "14" 2015 "15" 2016 "16" 2017 "17", nogrid glcolor(gs14)) ///
		ylabel(0 "0.0%" 0.4 "0.4%" 0.8 "0.8%" 1.2 "1.2%" 1.6 "1.6%" 2 "2.0%") ///
		xtitle("Year") ytitle("Net charge-offs (%)", size(small)) ///
	    legend(label(1 "Bank") label(2 "Credit Union") size(small)) 
graph export "$outputs_failure/graph_sum/chargeoffmortgage.jpg", replace	


* medium
twoway (scatter pct_chargeoff_bank_medium year, connect(l)) ///
	   (scatter pct_chargeoff_cu_medium year, connect(l)), ///
		xline(2007) ///
		xline(2009) ///
		xlabel(2004 "04" 2005 "05" 2006 "06" 2007 "07" 2008 "08" 2009 "09" 2010 "10" 2011 "11" 2012 "12" 2013 "13" 2014 "14" 2015 "15" 2016 "16" 2017 "17", nogrid glcolor(gs14)) ///
		ylabel(0 "0.0%" 0.1 "0.1%" 0.2 "0.2%" 0.3 "0.3%" 0.4 "0.4%" 0.5 "0.5%" 0.6 "0.6%" 0.7 "0.7") ///
		xtitle("Year") ytitle("Net charge-offs (%)", size(small)) ///
	    legend(label(1 "Bank") label(2 "Credit Union") size(small)) 
graph export "$outputs_failure/graph_sum/chargeoffmortgage_medium.jpg", replace	


* large
twoway (scatter pct_chargeoff_bank_large year, connect(l)) ///
	   (scatter pct_chargeoff_cu_large year, connect(l)), ///
		xline(2007) ///
		xline(2009) ///
		xlabel(2004 "04" 2005 "05" 2006 "06" 2007 "07" 2008 "08" 2009 "09" 2010 "10" 2011 "11" 2012 "12" 2013 "13" 2014 "14" 2015 "15" 2016 "16" 2017 "17", nogrid glcolor(gs14)) ///
		ylabel(0 "0.0%" 0.5 "0.5%" 1 "1.0%" 1.5 "1.5%" 2 "2.0%" 2.5 "2.5%") ///
		xtitle("Year") ytitle("Net charge-offs (%)", size(small)) ///
	    legend(label(1 "Bank") label(2 "Credit Union") size(small)) 
graph export "$outputs_failure/graph_sum/chargeoffmortgage_large.jpg", replace
restore

/* Comments: Super banks (>1b) contribute most to the difference in delinquency ratio */


